#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Created on Sat Mar 28 21:08:19 2020

@author: julianduggan
"""

## import packages
import tkinter #camelot depends on this
import camelot
import pandas as pd 
import glob
import os
import re
from datetime import datetime

## directories - UPDATE TO RUN ON NEW COMPUTER
datasavepath = "//zip/output"
pdfsavepath = "//zip/output/pdfs"
downloadfolderpath = "/path/to/your/default/download/folder"
chromedriverpath = "/path/to/your/chromedriver/executable/file"
today=datetime.today().strftime("%Y%m%d")

## set misc. options 
pd.options.display.max_colwidth = 400

## prepare dataset of PIDs
intermediate=pd.read_csv(str(glob.glob(datasavepath + "/intermediate pipeline/pipeline_all.csv")[0]))
already_processed=pd.read_csv(str(glob.glob(datasavepath + "/clean pipeline/clean_pipeline.csv")[0]))
intermediate=intermediate[~intermediate['id'].isin(already_processed.id)]

intermediate=intermediate.assign(pidnum_check = '')
intermediate=intermediate.assign(preP160766 = '')

intermediate=intermediate.assign(pages1='', pages2='', pages3='', pages4='', pages5='', pages6 = '')
intermediate=intermediate.assign(concept1='', concept2='', concept3='', concept4='', concept5='', concept6 = '')
intermediate=intermediate.assign(appraise1='', appraise2='', appraise3='', appraise4='', appraise5='', appraise6 = '')

intermediate=intermediate.assign(region1='', region2='', region3='', region4='', region5='', region6 = '')
intermediate=intermediate.assign(est_appraisal_date1 = '', est_appraisal_date2 = '', est_appraisal_date3 = '', est_appraisal_date4 = '', est_appraisal_date5 = '', est_appraisal_date6 = '')
intermediate=intermediate.assign(est_board_date1 = '', est_board_date2 = '', est_board_date3 = '', est_board_date4 = '', est_board_date5 = '', est_board_date6 = '')

intermediate=intermediate.assign(region_index1='', region_index2='', region_index3='', region_index4='', region_index5='', region_index6 = '')
intermediate=intermediate.assign(est_appraisal_date_index1 = '', est_appraisal_date_index2 = '', est_appraisal_date_index3 = '', est_appraisal_date_index4 = '', est_appraisal_date_index5 = '', est_appraisal_date_index6 = '')
intermediate=intermediate.assign(est_board_date_index1 = '', est_board_date_index2 = '', est_board_date_index3 = '', est_board_date_index4 = '', est_board_date_index5 = '', est_board_date_index6 = '')

intermediate=intermediate.assign(region_num1='', region_num2='', region_num3='', region_num4='', region_num5='', region_num6 = '')
intermediate=intermediate.assign(est_appraisal_date_num1 = '', est_appraisal_date_num2 = '', est_appraisal_date_num3 = '', est_appraisal_date_num4 = '', est_appraisal_date_num5 = '', est_appraisal_date_num6 = '')
intermediate=intermediate.assign(est_board_date_num1 = '', est_board_date_num2 = '', est_board_date_num3 = '', est_board_date_num4 = '', est_board_date_num5 = '', est_board_date_num6 = '')

## loop over each PID 
piddocs = glob.glob(pdfsavepath+'/*')
pidlist = intermediate['id'].tolist()
###intermediate2=intermediate[~intermediate.pidnum.isin([0])]
for pid in pidlist:
    
    # Determine how many documents we have downloaded for this pid
    thesedocs = [str for str in piddocs if any(sub in str for sub in [pid])]
    intermediate.loc[intermediate['id'] == pid, 'pidnum_check'] = len(thesedocs)
    
    # flag if pre-P160766 when format was different, requires different processing
    if len(pid) != 7:
        raise Exception('This PID is weird. Investigate!')
    
    if int(pid[1:7])< 160766:
        pidind = 1
        intermediate.loc[intermediate['id'] == pid, 'preP160766'] = 1
        
    if int(pid[1:7])>=160766:
        pidind = 0
        intermediate.loc[intermediate['id'] == pid, 'preP160766'] = 0
       
    # Loop over documents if there are any    
    docnum = 1
    for doc in thesedocs: 
        
        strdocnum=str(docnum)
        
        # Determine if this is a standard format PID document
        if len(thesedocs) == 1 and pid != "P165923" and pid != "P172342":
            suffix = ""
        if len(thesedocs) > 1 or pid == "P165923" or pid == "P172342":
            suffix = "_"+strdocnum    
        print('now on '+pid+suffix)
        page = camelot.read_pdf(pdfsavepath+"/"+pid+suffix+".pdf", pages="1", flavor='stream', strip_text=' .\n')
        intermediate.loc[intermediate['id'] == pid, 'pages'+strdocnum] = page.n   
        if page.n == 0: 
            continue 
        
        # Determine document's stage (Concept, Appraisal)
        table=page[0].df
        onelongcolumn=pd.DataFrame()
        for a in range(0,len(table.iloc[0])):
            col=table[a].to_frame()
            col=col.rename(columns={a:"temp"})
            onelongcolumn=pd.concat([onelongcolumn, col])
        onelongcolumn.reset_index(drop=True, inplace=True)
        concept=len(onelongcolumn[onelongcolumn['temp'].str.lower().str.contains('conceptstage')])
        appraisal=len(onelongcolumn[onelongcolumn['temp'].str.lower().str.contains('appraisalstage')])
        intermediate.loc[intermediate['id'] == pid, 'concept'+strdocnum] = concept   
        intermediate.loc[intermediate['id'] == pid, 'appraise'+strdocnum] = appraisal   
      
        # Process main table 
        if pidind == 0 or pid == 'P158079' or pid == 'P158372': 
            page = camelot.read_pdf(pdfsavepath+"/"+pid+suffix+".pdf", pages="2", flavor='stream',edge_tol=500,  strip_text=' .\n')
            if page.n == 0: 
                continue       
            table=page[0].df
            onelongcolumn=pd.DataFrame()
            for a in range(0,len(table.iloc[0])):
                col=table[a][[re.compile('[^a-zA-Z1234567890]').sub('',str).isalnum() for str in table[a]]].to_frame()
                col=col.rename(columns={a:"temp"})
                onelongcolumn=pd.concat([onelongcolumn, col])
            onelongcolumn.reset_index(drop=True, inplace=True)
                        
            # Find Region (just as a process check)
            region_index=onelongcolumn.index[onelongcolumn['temp'].str.lower().str.contains('region')].tolist()
            intermediate.loc[intermediate['id'] == pid, 'region_num'+strdocnum] = len(region_index)       
            if len(region_index) == 1:
                intermediate.loc[intermediate['id'] == pid, 'region_index'+strdocnum] = region_index[0]
                intermediate.loc[intermediate['id'] == pid, 'region'+strdocnum] = str(onelongcolumn.iloc[region_index[0]+1][0])
           
            # Find Appraisal Date
            appraisal_index=onelongcolumn.index[(onelongcolumn['temp'].str.lower().str.contains('apprais')) & (onelongcolumn['temp'].str.lower().str.contains('date'))].tolist()
            intermediate.loc[intermediate['id'] == pid, 'est_appraisal_date_num'+strdocnum] = len(appraisal_index)       
            if len(appraisal_index) == 1:
                intermediate.loc[intermediate['id'] == pid, 'est_appraisal_date_index'+strdocnum] = appraisal_index[0]
                intermediate.loc[intermediate['id'] == pid, 'est_appraisal_date'+strdocnum] = str(onelongcolumn.iloc[appraisal_index[0]+1][0])     
                       
            # Find Board Date
            board_index=onelongcolumn.index[(onelongcolumn['temp'].str.lower().str.contains('board')) & (onelongcolumn['temp'].str.lower().str.contains('date'))].tolist()
            intermediate.loc[intermediate['id'] == pid, 'est_board_date_num'+strdocnum] = len(board_index)       
            if len(board_index) == 1:
                intermediate.loc[intermediate['id'] == pid, 'est_board_date_index'+strdocnum] = board_index[0]
                intermediate.loc[intermediate['id'] == pid, 'est_board_date'+strdocnum] = str(onelongcolumn.iloc[board_index[0]+1][0]) 
                
        # Process secondary table
        if pidind == 1 and pid != 'P158079' and pid != 'P158372': 
            page = camelot.read_pdf(pdfsavepath+"/"+pid+suffix+".pdf", pages="1", line_scale=40,  strip_text=' .\n')              
            if page.n == 0: 
                continue       
            table=page[0].df
            for a in range(0,len(table.iloc[0])-1):
                
                # Find Region (just as a process check)
                region_index=table.index[table[a].str.lower().str.contains('region')].tolist()
                if len(region_index) == 1 and intermediate.loc[intermediate['id'] == pid, 'region_num'+strdocnum].tolist()[0] == '':
                    intermediate.loc[intermediate['id'] == pid, 'region_num'+strdocnum] = len(region_index)
                    intermediate.loc[intermediate['id'] == pid, 'region_index'+strdocnum] = region_index[0]
                    intermediate.loc[intermediate['id'] == pid, 'region'+strdocnum] = str(table.iloc[region_index[0]][a+1])
                    if str(table.iloc[region_index[0]][a+1]) == '' and a< len(table.iloc[0])-2:
                        intermediate.loc[intermediate['id'] == pid, 'region'+strdocnum] = str(table.iloc[region_index[0]][a+2])
                
                # Find Appraisal Date
                appraisal_index=table.index[(table[a].str.lower().str.contains('apprais')) & (table[a].str.lower().str.contains('date'))].tolist()
                if len(appraisal_index) == 1:
                    intermediate.loc[intermediate['id'] == pid, 'est_appraisal_date_num'+strdocnum] = len(appraisal_index)
                    intermediate.loc[intermediate['id'] == pid, 'est_appraisal_date_index'+strdocnum] = appraisal_index[0]
                    intermediate.loc[intermediate['id'] == pid, 'est_appraisal_date'+strdocnum] = str(table.iloc[appraisal_index[0]][a+1])
                    if str(table.iloc[appraisal_index[0]][a+1]) == '' and a< len(table.iloc[0])-2:
                        intermediate.loc[intermediate['id'] == pid, 'est_appraisal_date'+strdocnum] = str(table.iloc[appraisal_index[0]][a+2])
                         
                # Find Board Date
                board_index=table.index[(((table[a].str.lower().str.contains('board')) & (table[a].str.lower().str.contains('date')))|table[a].str.lower().str.contains('estimateddateofapproval'))].tolist()
                if len(board_index) == 1:
                    intermediate.loc[intermediate['id'] == pid, 'est_board_date_num'+strdocnum] = len(board_index)       
                    intermediate.loc[intermediate['id'] == pid, 'est_board_date_index'+strdocnum] = board_index[0]
                    intermediate.loc[intermediate['id'] == pid, 'est_board_date'+strdocnum] = str(table.iloc[board_index[0]][a+1])
                    if str(table.iloc[board_index[0]][a+1]) == '' and a< len(table.iloc[0])-2:
                        intermediate.loc[intermediate['id'] == pid, 'est_board_date'+strdocnum] = str(table.iloc[board_index[0]][a+2])
      
        docnum = docnum+1 
        
## Export 
last_chunk = max(glob.glob(datasavepath+"/intermediate pipeline/scraped_pipeline_2*"), key=os.path.getctime)
nextindex=int(last_chunk.split("/")[len(last_chunk.split("/"))-1].split("_")[3].split(".")[0])+1
intermediate.to_csv(datasavepath + '/intermediate pipeline/scraped_pipeline_'+today+'_'+str(nextindex)+'.csv', encoding = 'utf-8')

clean_pipeline=pd.DataFrame()
processed_chunks=glob.glob(datasavepath + "/intermediate pipeline/scraped_pipeline_2*")
for c in processed_chunks:
    chunk=pd.read_csv(c)
    chunk=chunk[chunk.pidnum_check.notnull()]
    clean_pipeline=clean_pipeline.append(chunk)
clean_pipeline.to_csv(datasavepath + '/intermediate pipeline/scraped_pipeline_all.csv', encoding = 'utf-8')